﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Reflection;

namespace Grain.Crm.ExcelProcessor
{
    public class XlsProcessor
    {
        /// <summary>
        /// Gets a table as collection of the specified sheet-name found in given file.
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetName"></param>
        /// <param name="collection"></param>
        public static void QueryData(string filePath, string sheetName, out EnumerableRowCollection<DataRow> collection)
        {
            if (!File.Exists(filePath))
                throw new InvalidOperationException("File not found. Path: " + filePath);


            var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; data source={0}; Extended Properties='Excel 12.0 Xml;HDR=NO';", filePath);

            var adapter = new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", connectionString);
            var ds = new DataSet();

            adapter.Fill(ds, "dataTable");

            DataTable data = ds.Tables["dataTable"];

            collection = data.AsEnumerable();
        }

    }
}
